/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.ProgramCellResultDao;
import com.elcom.emocbc.dbmapping.tables.pojos.ProgramCellResult;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class ProgramCellResultPersistance extends ProgramCellResultDao {

    private final com.elcom.emocbc.dbmapping.tables.ProgramCellResult PROGRAM_CELL_RESULT = com.elcom.emocbc.dbmapping.tables.ProgramCellResult.PROGRAM_CELL_RESULT;

    public ProgramCellResultPersistance() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<ProgramCellResult> find(String scheduleId, String cellKey) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(PROGRAM_CELL_RESULT)
                    .where(PROGRAM_CELL_RESULT.SCHEDULE_ID.eq(scheduleId))
                    .and(PROGRAM_CELL_RESULT.CELL_KEY.eq(cellKey))
                    .fetchInto(ProgramCellResult.class);
        } finally {
            DbAccess.closeConnection(con);
        }
    }

    public int getSuccessCount(String scheduleId, String bscId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(PROGRAM_CELL_RESULT)
                    .where(PROGRAM_CELL_RESULT.SCHEDULE_ID.eq(scheduleId))
                    .and(PROGRAM_CELL_RESULT.BSC_ID.eq(bscId))
                    .and(PROGRAM_CELL_RESULT.BROADCAST_RESULT.eq("Success"))
                    .fetchCount();
        } finally {
            DbAccess.closeConnection(con);
        }
    }

    public int getTotalCount(String scheduleId, String bscId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(PROGRAM_CELL_RESULT)
                    .where(PROGRAM_CELL_RESULT.SCHEDULE_ID.eq(scheduleId))
                    .and(PROGRAM_CELL_RESULT.BSC_ID.eq(bscId))
                    .fetchCount();
        } finally {
            DbAccess.closeConnection(con);
        }
    }

    public int delete(String scheduleId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.delete(PROGRAM_CELL_RESULT)
                    .where(PROGRAM_CELL_RESULT.SCHEDULE_ID.eq(scheduleId))
                    .execute();
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
    
    public int updateProgramCellResult(String scheduleId, String cellKey,String broadcastResult) throws SQLException{
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.update(PROGRAM_CELL_RESULT)
                    .set(PROGRAM_CELL_RESULT.BROADCAST_RESULT, broadcastResult)
                    .where(PROGRAM_CELL_RESULT.SCHEDULE_ID.eq(scheduleId))
                    .and(PROGRAM_CELL_RESULT.CELL_KEY.eq(cellKey))
                    .execute();
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
}
